import sqlite3
import os

# 创建或连接到SQLite数据库
db_path = os.path.join('c:\\Users\\Administrator\\RuoYi-Vue01\\sql', 'media_labels.db')
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 创建标签字典表
cursor.execute('''
CREATE TABLE IF NOT EXISTS media_user_labels_dict (
    label_id INTEGER PRIMARY KEY,
    label_name TEXT NOT NULL,
    label_code TEXT NOT NULL,
    label_desc TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
''')

# 创建标签类型字典表
cursor.execute('''
CREATE TABLE IF NOT EXISTS media_label_types_dict (
    type_id INTEGER PRIMARY KEY,
    type_name TEXT NOT NULL,
    type_code TEXT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
''')

# 清空表数据（避免重复插入）
cursor.execute('DELETE FROM media_user_labels_dict')
cursor.execute('DELETE FROM media_label_types_dict')

# 插入标签数据
labels_data = [
    (1, '新用户', 'NEW_USER', '新注册的广电用户'),
    (2, '潜在流失用户', 'POTENTIAL_CHURN', '有流失风险的用户'),
    (3, '一般用户', 'REGULAR_USER', '普通活跃度用户'),
    (4, '忠诚用户', 'LOYAL_USER', '高忠诚度用户'),
    (5, '高价值用户', 'HIGH_VALUE', '消费能力强的用户')
]
cursor.executemany('''
INSERT INTO media_user_labels_dict (label_id, label_name, label_code, label_desc)
VALUES (?, ?, ?, ?)
''', labels_data)

# 插入标签类型数据
types_data = [
    (1, '规则标签', 'rule'),
    (2, '随机标签', 'random')
]
cursor.executemany('''
INSERT INTO media_label_types_dict (type_id, type_name, type_code)
VALUES (?, ?, ?)
''', types_data)

# 提交事务
conn.commit()

# 查询验证
print("=== 标签字典表数据 ===")
cursor.execute('SELECT * FROM media_user_labels_dict')
for row in cursor.fetchall():
    print(row)

print("\n=== 标签类型表数据 ===")
cursor.execute('SELECT * FROM media_label_types_dict')
for row in cursor.fetchall():
    print(row)

# 关闭连接
conn.close()
print(f"\n字典表创建并插入数据完成！数据库保存在: {db_path}")